﻿using DrCarDrive.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DrCarDrive.DBService
{
    class DriveService
    {
        private DrcardriveDB db;

        public DriveService()
        {
            db = new DrcardriveDB();
        }

        public int save(tbl_drive drive)
        {
            DateTime currentDateTime = DateTime.Now;

            drive.INSERT_DATETIME = currentDateTime;
            drive.UPDATE_DATETIME = currentDateTime;

            if (drive != null)
            {
                db.Drives.Add(drive);
            }
            return db.SaveChanges();
        }

        internal int update(tbl_drive drive)
        {
            DateTime currentDateTime = DateTime.Now;
            drive.UPDATE_DATETIME = currentDateTime;

            if (drive != null)
            {
                var original = db.Drives.SingleOrDefault(d => d.ID.Equals(drive.ID));
                db.Entry(original).CurrentValues.SetValues(drive);
            }
            return db.SaveChanges();
        }

        public List<tbl_drive> viewAll()
        {
            return db.Drives.ToList();
        }

        public tbl_drive viewByCarRegNo(string regNo)
        {
            return null;
        }

        public tbl_drive viewByCarIDLastRecord(string carID)
        {
            return db.Drives.Where(d => d.FKY_CAR.Equals(carID)).OrderByDescending(d => d.UPDATE_DATETIME).FirstOrDefault();
        }

        internal List<DriveHistoryView> viewHistoryRecordByCarID(string carID)
        {

            var query= (from d in db.Drives
                    where d.FKY_CAR == carID
                    join p in db.Places on d.ID equals p.FKY_DRIVE into pl
                    join f in db.Fuel on d.ID equals f.FKY_DRIVE into fd
                    join e in db.Expenses on d.ID equals e.FKY_DRIVE into ex
                    select new DriveHistoryView()
                    {
                        DriveID = d.ID,
                        DriverName = d.DRIVER_NAME,
                        StartDate = d.START_DATE,
                        StartOdometer = d.START_ODOMETER,
                        EndDate = d.END_DATE,
                        EndOdometer = d.END_ODOMETER,
                        From = d.FROM,
                        To = d.TO,
                        Places = pl,
                        FuelTopup = fd,
                        //PlacesList = String.Join(",",pls=>pls.VILLAGE_NAME.ToArray()),
                        NumPatients = pl.Sum(pls=>pls.NUMBER_PATIENT),
                        NumDoctors = pl.Sum(pls=>pls.NUMBER_DOCTORS),
                        NumParamedics = pl.Sum(pls=>pls.NUMBER_PARAMEDICS)
                    }).ToList();

            return query;

        }

        internal List<DriveHistoryView> viewHistoryRecordByDriveID(string driveID)
        {

            var query = (from d in db.Drives
                         where d.ID == driveID
                         join p in db.Places on d.ID equals p.FKY_DRIVE into pl
                         join f in db.Fuel on d.ID equals f.FKY_DRIVE into fd
                         join e in db.Expenses on d.ID equals e.FKY_DRIVE into ex
                         select new DriveHistoryView()
                         {
                             DriveID = d.ID,
                             DriverName = d.DRIVER_NAME,
                             StartDate = d.START_DATE,
                             StartOdometer = d.START_ODOMETER,
                             EndDate = d.END_DATE,
                             EndOdometer = d.END_ODOMETER,
                             From = d.FROM,
                             To = d.TO,
                             Places = pl,
                             FuelTopup = fd,
                             //PlacesList = String.Join(",",pls=>pls.VILLAGE_NAME.ToArray()),
                             NumPatients = pl.Sum(pls => pls.NUMBER_PATIENT),
                             NumDoctors = pl.Sum(pls => pls.NUMBER_DOCTORS),
                             NumParamedics = pl.Sum(pls => pls.NUMBER_PARAMEDICS)
                         }).ToList();

           return query;

        }

        internal tbl_drive viewByCarIDStartDate(string carID, DateTime? date)
        {
            //return db.Drives.Where(d => d.START_DATE.Value.Date == date && d.FKY_CAR == carID).FirstOrDefault();

            var query = (from d in db.Drives
                        // where d.FKY_CAR == carID && DateTime.Compare(d.START_DATE.Value.Date,date.Value.Date)==0
                        where d.FKY_CAR == carID 
                        && d.START_DATE.Value.Year == date.Value.Year
                        && d.START_DATE.Value.Month == date.Value.Month
                        && d.START_DATE.Value.Day == date.Value.Day
                        select d).FirstOrDefault();

            return query;
        }
    }
}
